using System;
using System.Data;
using System.Configuration;
using CodeWork.Library.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Data.SqlTypes;
using CodeWork.Library;
using System.Text;
using System.Data.SqlClient;

/// <summary>
/// ORM object that maps to tbl_Product
/// </summary>
namespace EasyCommerce.DAL
{
    public class Product : SqlServerDAO
    {
        /// <summary>
        /// Self table parameters
        /// </summary>
        private int _id;
		private string _productName;
		private string _productDefaultImageUrl;
		private double _productPrice;
		private int _productBrandId;
		private int _productCategoryId;
		private int _mainProductId;
		private string _differBy;
		private bool _isProductInSale;
		private double _saleTimePrice;
		private double _rating;
		private int _totalRater;
		private bool _isActive;
		private bool _isFeatured;
		private bool _isNewArrival;
		private bool _isBestSeller;
		private double _productLength;
		private double _productWidth;
		private double _productHeight;
		private double _productWieght;
		private string _productSizeUnit;
		private string _productWeightUnit;
		private int _itemInStock;
		private string _sortToken;

        /// <summary>
        /// Foriegn key parameters
        /// </summary>
        private List<Product> _mainProduct;
		private bool _isMainProductLoaded;
		private List<ReviewComment> _reviewReviewComment;
		private bool _isReviewReviewCommentLoaded;
		private List<SaleOrder> _itemSaleOrder;
		private bool _isItemSaleOrderLoaded;
		private List<ShoppingCart> _itemShoppingCart;
		private bool _isItemShoppingCartLoaded;
		

        /// <summary>
        /// Self table properties
        /// </summary>
		public int Id
		{
			get { return this._id; }
			set { this._id = value; }
		}

		public string ProductName
		{
			get { return this._productName; }
			set { this._productName = value; }
		}

		public string ProductDefaultImageUrl
		{
			get { return this._productDefaultImageUrl; }
			set { this._productDefaultImageUrl = value; }
		}

		public double ProductPrice
		{
			get { return this._productPrice; }
			set { this._productPrice = value; }
		}

		public int ProductBrandId
		{
			get { return this._productBrandId; }
			set { this._productBrandId = value; }
		}

		public int ProductCategoryId
		{
			get { return this._productCategoryId; }
			set { this._productCategoryId = value; }
		}

		public int MainProductId
		{
			get { return this._mainProductId; }
			set { this._mainProductId = value; }
		}

		public string DifferBy
		{
			get { return this._differBy; }
			set { this._differBy = value; }
		}

		public bool IsProductInSale
		{
			get { return this._isProductInSale; }
			set { this._isProductInSale = value; }
		}

		public double SaleTimePrice
		{
			get { return this._saleTimePrice; }
			set { this._saleTimePrice = value; }
		}

		public double Rating
		{
			get { return this._rating; }
			set { this._rating = value; }
		}

		public int TotalRater
		{
			get { return this._totalRater; }
			set { this._totalRater = value; }
		}

		public bool IsActive
		{
			get { return this._isActive; }
			set { this._isActive = value; }
		}

		public bool IsFeatured
		{
			get { return this._isFeatured; }
			set { this._isFeatured = value; }
		}

		public bool IsNewArrival
		{
			get { return this._isNewArrival; }
			set { this._isNewArrival = value; }
		}

		public bool IsBestSeller
		{
			get { return this._isBestSeller; }
			set { this._isBestSeller = value; }
		}

		public double ProductLength
		{
			get { return this._productLength; }
			set { this._productLength = value; }
		}

		public double ProductWidth
		{
			get { return this._productWidth; }
			set { this._productWidth = value; }
		}

		public double ProductHeight
		{
			get { return this._productHeight; }
			set { this._productHeight = value; }
		}

		public double ProductWieght
		{
			get { return this._productWieght; }
			set { this._productWieght = value; }
		}

		public string ProductSizeUnit
		{
			get { return this._productSizeUnit; }
			set { this._productSizeUnit = value; }
		}

		public string ProductWeightUnit
		{
			get { return this._productWeightUnit; }
			set { this._productWeightUnit = value; }
		}

		public int ItemInStock
		{
			get { return this._itemInStock; }
			set { this._itemInStock = value; }
		}

		public string SortToken
		{
			get { return this._sortToken; }
			set { this._sortToken = value; }
		}
		
        /// <summary>
        /// Foreign key properties, loaded on demand only
        /// </summary>
        public List<Product> MainProduct
		{
			get
			{
				if (!this._isMainProductLoaded)
				{
					List<Condition> conditions = new List<Condition>();
					conditions.Add(new Condition("main_product_id", ConditionOperator.Equal,this._id, JoinOperator.FirstValue));
					Product product = new Product();
					this._mainProduct = product.Load(conditions);
					this._isMainProductLoaded = true;
				}
				return this._mainProduct;
			}
			set
			{
				this._mainProduct = value;
				this._isMainProductLoaded = true;
			}
		}
		public List<ReviewComment> ReviewReviewComment
		{
			get
			{
				if (!this._isReviewReviewCommentLoaded)
				{
					List<Condition> conditions = new List<Condition>();
					conditions.Add(new Condition("review_for_product_id", ConditionOperator.Equal,this._id, JoinOperator.FirstValue));
					ReviewComment reviewComment = new ReviewComment();
					this._reviewReviewComment = reviewComment.Load(conditions);
					this._isReviewReviewCommentLoaded = true;
				}
				return this._reviewReviewComment;
			}
			set
			{
				this._reviewReviewComment = value;
				this._isReviewReviewCommentLoaded = true;
			}
		}
		public List<SaleOrder> ItemSaleOrder
		{
			get
			{
				if (!this._isItemSaleOrderLoaded)
				{
					List<Condition> conditions = new List<Condition>();
					conditions.Add(new Condition("item_product_id", ConditionOperator.Equal,this._id, JoinOperator.FirstValue));
					SaleOrder saleOrder = new SaleOrder();
					this._itemSaleOrder = saleOrder.Load(conditions);
					this._isItemSaleOrderLoaded = true;
				}
				return this._itemSaleOrder;
			}
			set
			{
				this._itemSaleOrder = value;
				this._isItemSaleOrderLoaded = true;
			}
		}
		public List<ShoppingCart> ItemShoppingCart
		{
			get
			{
				if (!this._isItemShoppingCartLoaded)
				{
					List<Condition> conditions = new List<Condition>();
					conditions.Add(new Condition("item_product_id", ConditionOperator.Equal,this._id, JoinOperator.FirstValue));
					ShoppingCart shoppingCart = new ShoppingCart();
					this._itemShoppingCart = shoppingCart.Load(conditions);
					this._isItemShoppingCartLoaded = true;
				}
				return this._itemShoppingCart;
			}
			set
			{
				this._itemShoppingCart = value;
				this._isItemShoppingCartLoaded = true;
			}
		}
		

        /// <summary>
        /// Empty Constructor
        /// </summary>
        public Product() { }

        /// <summary>
        /// Parameterized constructor
        /// </summary>
        /// <param name="id"></param>
		/// <param name="productName"></param>
		/// <param name="productDefaultImageUrl"></param>
		/// <param name="productPrice"></param>
		/// <param name="productBrandId"></param>
		/// <param name="productCategoryId"></param>
		/// <param name="mainProductId"></param>
		/// <param name="differBy"></param>
		/// <param name="isProductInSale"></param>
		/// <param name="saleTimePrice"></param>
		/// <param name="rating"></param>
		/// <param name="totalRater"></param>
		/// <param name="isActive"></param>
		/// <param name="isFeatured"></param>
		/// <param name="isNewArrival"></param>
		/// <param name="isBestSeller"></param>
		/// <param name="productLength"></param>
		/// <param name="productWidth"></param>
		/// <param name="productHeight"></param>
		/// <param name="productWieght"></param>
		/// <param name="productSizeUnit"></param>
		/// <param name="productWeightUnit"></param>
		/// <param name="itemInStock"></param>
		/// <param name="sortToken"></param>
        public Product
        (
            int id,
			string productName,
			string productDefaultImageUrl,
			double productPrice,
			int productBrandId,
			int productCategoryId,
			int mainProductId,
			string differBy,
			bool isProductInSale,
			double saleTimePrice,
			double rating,
			int totalRater,
			bool isActive,
			bool isFeatured,
			bool isNewArrival,
			bool isBestSeller,
			double productLength,
			double productWidth,
			double productHeight,
			double productWieght,
			string productSizeUnit,
			string productWeightUnit,
			int itemInStock,
			string sortToken
        )
        {
            this._id = id;
			this._productName = productName;
			this._productDefaultImageUrl = productDefaultImageUrl;
			this._productPrice = productPrice;
			this._productBrandId = productBrandId;
			this._productCategoryId = productCategoryId;
			this._mainProductId = mainProductId;
			this._differBy = differBy;
			this._isProductInSale = isProductInSale;
			this._saleTimePrice = saleTimePrice;
			this._rating = rating;
			this._totalRater = totalRater;
			this._isActive = isActive;
			this._isFeatured = isFeatured;
			this._isNewArrival = isNewArrival;
			this._isBestSeller = isBestSeller;
			this._productLength = productLength;
			this._productWidth = productWidth;
			this._productHeight = productHeight;
			this._productWieght = productWieght;
			this._productSizeUnit = productSizeUnit;
			this._productWeightUnit = productWeightUnit;
			this._itemInStock = itemInStock;
			this._sortToken = sortToken;
        }

        /// <summary>
        /// Static save method. Saves the current instance in the table using the provided values using the
        /// primary key id. Expects that the Id is a primary key of an existing record in table.
        /// </summary>
        /// <param name="id"></param>
		/// <param name="productName"></param>
		/// <param name="productDefaultImageUrl"></param>
		/// <param name="productPrice"></param>
		/// <param name="productBrandId"></param>
		/// <param name="productCategoryId"></param>
		/// <param name="mainProductId"></param>
		/// <param name="differBy"></param>
		/// <param name="isProductInSale"></param>
		/// <param name="saleTimePrice"></param>
		/// <param name="rating"></param>
		/// <param name="totalRater"></param>
		/// <param name="isActive"></param>
		/// <param name="isFeatured"></param>
		/// <param name="isNewArrival"></param>
		/// <param name="isBestSeller"></param>
		/// <param name="productLength"></param>
		/// <param name="productWidth"></param>
		/// <param name="productHeight"></param>
		/// <param name="productWieght"></param>
		/// <param name="productSizeUnit"></param>
		/// <param name="productWeightUnit"></param>
		/// <param name="itemInStock"></param>
		/// <param name="sortToken"></param>
        /// <returns>Boolean result as success or failure</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public static bool Save
        (
            int id,
			string productName,
			string productDefaultImageUrl,
			double productPrice,
			int productBrandId,
			int productCategoryId,
			int mainProductId,
			string differBy,
			bool isProductInSale,
			double saleTimePrice,
			double rating,
			int totalRater,
			bool isActive,
			bool isFeatured,
			bool isNewArrival,
			bool isBestSeller,
			double productLength,
			double productWidth,
			double productHeight,
			double productWieght,
			string productSizeUnit,
			string productWeightUnit,
			int itemInStock,
			string sortToken
        )
        {
            Product product = new Product();
            product.Id = id;
			product.ProductName = productName;
			product.ProductDefaultImageUrl = productDefaultImageUrl;
			product.ProductPrice = productPrice;
			product.ProductBrandId = productBrandId;
			product.ProductCategoryId = productCategoryId;
			product.MainProductId = mainProductId;
			product.DifferBy = differBy;
			product.IsProductInSale = isProductInSale;
			product.SaleTimePrice = saleTimePrice;
			product.Rating = rating;
			product.TotalRater = totalRater;
			product.IsActive = isActive;
			product.IsFeatured = isFeatured;
			product.IsNewArrival = isNewArrival;
			product.IsBestSeller = isBestSeller;
			product.ProductLength = productLength;
			product.ProductWidth = productWidth;
			product.ProductHeight = productHeight;
			product.ProductWieght = productWieght;
			product.ProductSizeUnit = productSizeUnit;
			product.ProductWeightUnit = productWeightUnit;
			product.ItemInStock = itemInStock;
			product.SortToken = sortToken;

            return product.Save();
        }

        /// <summary>
        /// Saves the current instance and returns result
        /// </summary>
        /// <returns>Boolean result as success or failure</returns>
        public override bool Save()
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, Id);
			AddCommandParameter(command, "@ProductName", SqlDbType.NVarChar, 250, ParameterDirection.Input, ProductName);
			AddCommandParameter(command, "@ProductDefaultImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, ProductDefaultImageUrl);
			AddCommandParameter(command, "@ProductPrice", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductPrice);
			AddCommandParameter(command, "@ProductBrandId", SqlDbType.Int, 0, ParameterDirection.Input, ProductBrandId);
			AddCommandParameter(command, "@ProductCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, ProductCategoryId);
			AddCommandParameter(command, "@MainProductId", SqlDbType.Int, 0, ParameterDirection.Input, MainProductId);
			AddCommandParameter(command, "@DifferBy", SqlDbType.NVarChar, 50, ParameterDirection.Input, DifferBy);
			AddCommandParameter(command, "@IsProductInSale", SqlDbType.Bit, 0, ParameterDirection.Input, IsProductInSale);
			AddCommandParameter(command, "@SaleTimePrice", SqlDbType.Decimal, 0, ParameterDirection.Input, SaleTimePrice);
			AddCommandParameter(command, "@Rating", SqlDbType.Decimal, 0, ParameterDirection.Input, Rating);
			AddCommandParameter(command, "@TotalRater", SqlDbType.Int, 0, ParameterDirection.Input, TotalRater);
			AddCommandParameter(command, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, IsActive);
			AddCommandParameter(command, "@IsFeatured", SqlDbType.Bit, 0, ParameterDirection.Input, IsFeatured);
			AddCommandParameter(command, "@IsNewArrival", SqlDbType.Bit, 0, ParameterDirection.Input, IsNewArrival);
			AddCommandParameter(command, "@IsBestSeller", SqlDbType.Bit, 0, ParameterDirection.Input, IsBestSeller);
			AddCommandParameter(command, "@ProductLength", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductLength);
			AddCommandParameter(command, "@ProductWidth", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductWidth);
			AddCommandParameter(command, "@ProductHeight", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductHeight);
			AddCommandParameter(command, "@ProductWieght", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductWieght);
			AddCommandParameter(command, "@ProductSizeUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, ProductSizeUnit);
			AddCommandParameter(command, "@ProductWeightUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, ProductWeightUnit);
			AddCommandParameter(command, "@ItemInStock", SqlDbType.Int, 0, ParameterDirection.Input, ItemInStock);
			AddCommandParameter(command, "@SortToken", SqlDbType.NVarChar, 50, ParameterDirection.Input, SortToken);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_Product_Save.ToString());

            int returnValue = (int)GetOneData(command);
            return (returnValue == 0 ? false : true);
        }

        /// <summary>
        /// Saves the current instance based on provided conditions. This method assumes that
        /// conditions are provided in correct order and in proper format.
        /// </summary>
        /// <param name="conditions">Custom condition based on which to match data</param>
        /// <returns>Boolean result as success or failure</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public bool Save(List<Condition> conditions)
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            PrepareConditionalParameters(command, conditions);

            AddCommandParameter(command, "@NewProductName", SqlDbType.NVarChar, 250, ParameterDirection.Input, ProductName);
			AddCommandParameter(command, "@NewProductDefaultImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, ProductDefaultImageUrl);
			AddCommandParameter(command, "@NewProductPrice", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductPrice);
			AddCommandParameter(command, "@NewProductBrandId", SqlDbType.Int, 0, ParameterDirection.Input, ProductBrandId);
			AddCommandParameter(command, "@NewProductCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, ProductCategoryId);
			AddCommandParameter(command, "@NewMainProductId", SqlDbType.Int, 0, ParameterDirection.Input, MainProductId);
			AddCommandParameter(command, "@NewDifferBy", SqlDbType.NVarChar, 50, ParameterDirection.Input, DifferBy);
			AddCommandParameter(command, "@NewIsProductInSale", SqlDbType.Bit, 0, ParameterDirection.Input, IsProductInSale);
			AddCommandParameter(command, "@NewSaleTimePrice", SqlDbType.Decimal, 0, ParameterDirection.Input, SaleTimePrice);
			AddCommandParameter(command, "@NewRating", SqlDbType.Decimal, 0, ParameterDirection.Input, Rating);
			AddCommandParameter(command, "@NewTotalRater", SqlDbType.Int, 0, ParameterDirection.Input, TotalRater);
			AddCommandParameter(command, "@NewIsActive", SqlDbType.Bit, 0, ParameterDirection.Input, IsActive);
			AddCommandParameter(command, "@NewIsFeatured", SqlDbType.Bit, 0, ParameterDirection.Input, IsFeatured);
			AddCommandParameter(command, "@NewIsNewArrival", SqlDbType.Bit, 0, ParameterDirection.Input, IsNewArrival);
			AddCommandParameter(command, "@NewIsBestSeller", SqlDbType.Bit, 0, ParameterDirection.Input, IsBestSeller);
			AddCommandParameter(command, "@NewProductLength", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductLength);
			AddCommandParameter(command, "@NewProductWidth", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductWidth);
			AddCommandParameter(command, "@NewProductHeight", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductHeight);
			AddCommandParameter(command, "@NewProductWieght", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductWieght);
			AddCommandParameter(command, "@NewProductSizeUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, ProductSizeUnit);
			AddCommandParameter(command, "@NewProductWeightUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, ProductWeightUnit);
			AddCommandParameter(command, "@NewItemInStock", SqlDbType.Int, 0, ParameterDirection.Input, ItemInStock);
			AddCommandParameter(command, "@NewSortToken", SqlDbType.NVarChar, 50, ParameterDirection.Input, SortToken);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_Product_SaveConditional.ToString());

            int returnValue = (int)GetOneData(command);
            return (returnValue == 0 ? false : true);
        }

        /// <summary>
        /// Static creational method. This method actually adds a new entry in the table with
        /// the provided information.
        /// </summary>
        /// <param name="productName"></param>
		/// <param name="productDefaultImageUrl"></param>
		/// <param name="productPrice"></param>
		/// <param name="productBrandId"></param>
		/// <param name="productCategoryId"></param>
		/// <param name="mainProductId"></param>
		/// <param name="differBy"></param>
		/// <param name="isProductInSale"></param>
		/// <param name="saleTimePrice"></param>
		/// <param name="rating"></param>
		/// <param name="totalRater"></param>
		/// <param name="isActive"></param>
		/// <param name="isFeatured"></param>
		/// <param name="isNewArrival"></param>
		/// <param name="isBestSeller"></param>
		/// <param name="productLength"></param>
		/// <param name="productWidth"></param>
		/// <param name="productHeight"></param>
		/// <param name="productWieght"></param>
		/// <param name="productSizeUnit"></param>
		/// <param name="productWeightUnit"></param>
		/// <param name="itemInStock"></param>
		/// <param name="sortToken"></param>
        /// <returns>Instance of the created object</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public static Product Create
        (
            string productName,
			string productDefaultImageUrl,
			double productPrice,
			int productBrandId,
			int productCategoryId,
			int mainProductId,
			string differBy,
			bool isProductInSale,
			double saleTimePrice,
			double rating,
			int totalRater,
			bool isActive,
			bool isFeatured,
			bool isNewArrival,
			bool isBestSeller,
			double productLength,
			double productWidth,
			double productHeight,
			double productWieght,
			string productSizeUnit,
			string productWeightUnit,
			int itemInStock,
			string sortToken
        )
        {
            Product product = new Product();
            product.ProductName = productName;
			product.ProductDefaultImageUrl = productDefaultImageUrl;
			product.ProductPrice = productPrice;
			product.ProductBrandId = productBrandId;
			product.ProductCategoryId = productCategoryId;
			product.MainProductId = mainProductId;
			product.DifferBy = differBy;
			product.IsProductInSale = isProductInSale;
			product.SaleTimePrice = saleTimePrice;
			product.Rating = rating;
			product.TotalRater = totalRater;
			product.IsActive = isActive;
			product.IsFeatured = isFeatured;
			product.IsNewArrival = isNewArrival;
			product.IsBestSeller = isBestSeller;
			product.ProductLength = productLength;
			product.ProductWidth = productWidth;
			product.ProductHeight = productHeight;
			product.ProductWieght = productWieght;
			product.ProductSizeUnit = productSizeUnit;
			product.ProductWeightUnit = productWeightUnit;
			product.ItemInStock = itemInStock;
			product.SortToken = sortToken;

            return (product.Create() ? product : null);
        }

        /// <summary>
        /// Instance creational method. Inserts the current object as a new entry in table.
        /// </summary>
        /// <returns>Boolean result as success or failure</returns>
        public override bool Create()
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@ProductName", SqlDbType.NVarChar, 250, ParameterDirection.Input, ProductName);
			AddCommandParameter(command, "@ProductDefaultImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, ProductDefaultImageUrl);
			AddCommandParameter(command, "@ProductPrice", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductPrice);
			AddCommandParameter(command, "@ProductBrandId", SqlDbType.Int, 0, ParameterDirection.Input, ProductBrandId);
			AddCommandParameter(command, "@ProductCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, ProductCategoryId);
			AddCommandParameter(command, "@MainProductId", SqlDbType.Int, 0, ParameterDirection.Input, MainProductId);
			AddCommandParameter(command, "@DifferBy", SqlDbType.NVarChar, 50, ParameterDirection.Input, DifferBy);
			AddCommandParameter(command, "@IsProductInSale", SqlDbType.Bit, 0, ParameterDirection.Input, IsProductInSale);
			AddCommandParameter(command, "@SaleTimePrice", SqlDbType.Decimal, 0, ParameterDirection.Input, SaleTimePrice);
			AddCommandParameter(command, "@Rating", SqlDbType.Decimal, 0, ParameterDirection.Input, Rating);
			AddCommandParameter(command, "@TotalRater", SqlDbType.Int, 0, ParameterDirection.Input, TotalRater);
			AddCommandParameter(command, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, IsActive);
			AddCommandParameter(command, "@IsFeatured", SqlDbType.Bit, 0, ParameterDirection.Input, IsFeatured);
			AddCommandParameter(command, "@IsNewArrival", SqlDbType.Bit, 0, ParameterDirection.Input, IsNewArrival);
			AddCommandParameter(command, "@IsBestSeller", SqlDbType.Bit, 0, ParameterDirection.Input, IsBestSeller);
			AddCommandParameter(command, "@ProductLength", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductLength);
			AddCommandParameter(command, "@ProductWidth", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductWidth);
			AddCommandParameter(command, "@ProductHeight", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductHeight);
			AddCommandParameter(command, "@ProductWieght", SqlDbType.Decimal, 0, ParameterDirection.Input, ProductWieght);
			AddCommandParameter(command, "@ProductSizeUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, ProductSizeUnit);
			AddCommandParameter(command, "@ProductWeightUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, ProductWeightUnit);
			AddCommandParameter(command, "@ItemInStock", SqlDbType.Int, 0, ParameterDirection.Input, ItemInStock);
			AddCommandParameter(command, "@SortToken", SqlDbType.NVarChar, 50, ParameterDirection.Input, SortToken);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_Product_Create.ToString());

            Id = (int)GetOneData(command);
            return (Id == 0 ? false : true);
        }

        /// <summary>
        /// Static deletion method. Deletes a record from table using the provided primary key id.
        /// </summary>
        /// <param name="id">Primary key</param>
        /// <returns>Boolean result as success or failure</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Delete, true)]
        public static bool Delete(int id)
        {
            Product product = new Product();
            product.Id = id;

            return product.Delete();
        }

        /// <summary>
        /// Static deletion method. Deletes a list record from table using the provided primary keys id.
        /// </summary>
        /// <param name="ids">List of primarky key id</param>
        /// <returns>Boolean result as success or failure</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Delete, true)]
        public static bool Delete(List<int> ids)
        {
            string csvIds = Utility.MakeCSV(ids);

            Product product = new Product();

            return product.Delete(csvIds);
        }

        /// <summary>
        /// Static deletion method. Deletes records from table using provided conditions.
        /// </summary>
        /// <param name="conditions">Custom condition based on which to match data</param>
        /// <returns>Boolean result as success or failure</returns>
        public static bool Delete(List<Condition> conditions)
        {
            Product product = new Product();

            return product.ConditionalDelete(conditions);
        }

        /// <summary>
        /// This is helper method for conditional delete method above. Not for other general use.
        /// </summary>
        /// <param name="conditions">Custom condition based on which to match data</param>
        /// <returns>Boolean result as success or failure</returns>
        private bool ConditionalDelete(List<Condition> conditions)
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            PrepareConditionalParameters(command, conditions);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_Product_DeleteConditional.ToString());

            int returnValue = (int)GetOneData(command);
            return (returnValue == 0 ? false : true);
        }

        /// <summary>
        /// This is a helper method for multiple id delete method. Not for general use.
        /// </summary>
        /// <param name="csvIds">Comma seperated primary key values as string</param>
        /// <returns>Boolean result as success or failure</returns>
        private bool Delete(string csvIds)
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@Ids", SqlDbType.NVarChar, 500, ParameterDirection.Input, csvIds);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_Product_DeleteMultiple.ToString());

            int returnValue = (int)GetOneData(command);
            return (returnValue == 0 ? false : true);
        }

        /// <summary>
        /// Instance deletion method. Deletes the current instance from table.
        /// </summary>
        /// <returns>Boolean result as success or failure</returns>
        public override bool Delete()
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, Id);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_Product_DeleteOne.ToString());

            int returnValue = (int)GetOneData(command);
            return (returnValue == 0 ? false : true);
        }

        /// <summary>
        /// Static selection method. Used for loading a perticular data using primary key id.
        /// </summary>
        /// <param name="id">Primary key</param>
        /// <returns>Instance of the loaded object</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static Product Load(int id)
        {
            Product product = new Product();
            product.Id = id;

            if (product.Load())
                return product;
            else
                return null;
        }

        /// <summary>
        /// Instance selection method that fetches data using paging and sorting and also with 
        /// custom conditions.
        /// </summary>
        /// <param name="oderBy">Sorting order</param>
        /// <param name="startRow">Page start position</param>
        /// <param name="pageSize">Number of maximum record to be shown</param>
        /// <param name="conditions">Custom condition based on which to match data</param>
        /// <returns>List of instances of loaded objects</returns>
        public List<Product> Load(string orderBy, int startRow, int pageSize, List<Condition> conditions)
        {
            List<Product> products = new List<Product>();
			
			SqlCommand command = (SqlCommand)CreateCommand();
			
            PrepareConditionalParameters(command, conditions);

            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@OrderBy", SqlDbType.NVarChar, 50, ParameterDirection.Input, orderBy);
            AddCommandParameter(command, "@StartRow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddCommandParameter(command, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_Product_LoadConditionalPaged.ToString());

            List<BaseDAO> dataList = GetListData(command);

            foreach (BaseDAO data in dataList)
            {
                Product product = (Product)data;
                products.Add(product);
            }

            return products;
        }

        /// <summary>
        /// Counts the number of total record available
        /// </summary>
        /// <param name="conditions">Custom condition based on which to match data</param>
        /// <returns>Count of the records</returns>
        public int LoadCount(List<Condition> conditions)
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            PrepareConditionalParameters(command, conditions);

            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_Product_LoadConditionalPagedCount.ToString());

            int returnValue = (int)GetOneData(command);
            return returnValue;
        }

        /// <summary>
        /// Instance selection method that fetches data using paging and sorting.
        /// </summary>
        /// <param name="oderBy">Sorting order</param>
        /// <param name="startRow">Page start position</param>
        /// <param name="pageSize">Number of maximum record to be shown</param>
        /// <returns>List of instances of loaded objects</returns>
        public List<Product> Load(string orderBy, int startRow, int pageSize)
        {
            List<Product> products = new List<Product>();
            
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@OrderBy", SqlDbType.NVarChar, 50, ParameterDirection.Input, orderBy);
            AddCommandParameter(command, "@StartRow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddCommandParameter(command, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_Product_LoadPaged.ToString());

            List<BaseDAO> dataList = GetListData(command);

            foreach (BaseDAO data in dataList)
            {
                Product product = (Product)data;
                products.Add(product);
            }

            return products;
        }

        /// <summary>
        /// Counts the number of total record available
        /// </summary>
        /// <returns>Count of the records</returns>
        public int LoadCount()
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_Product_LoadPagedCount.ToString());

            int returnValue = (int)GetOneData(command);
            return returnValue;
        }

        /// <summary>
        /// Instance selection method. Used for reloading the current object from table. This method uses
        /// the Id of the current object to load and expects that the Id is correct.
        /// </summary>
        /// <returns>Boolean result as success or failure</returns>
        public override bool Load()
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, Id);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_Product_Load.ToString());

            List<BaseDAO> dataList = GetListData(command);

            foreach (BaseDAO data in dataList)
            {
                Product product = (Product)data;

                this._id = product._id;
				this._productName = product._productName;
				this._productDefaultImageUrl = product._productDefaultImageUrl;
				this._productPrice = product._productPrice;
				this._productBrandId = product._productBrandId;
				this._productCategoryId = product._productCategoryId;
				this._mainProductId = product._mainProductId;
				this._differBy = product._differBy;
				this._isProductInSale = product._isProductInSale;
				this._saleTimePrice = product._saleTimePrice;
				this._rating = product._rating;
				this._totalRater = product._totalRater;
				this._isActive = product._isActive;
				this._isFeatured = product._isFeatured;
				this._isNewArrival = product._isNewArrival;
				this._isBestSeller = product._isBestSeller;
				this._productLength = product._productLength;
				this._productWidth = product._productWidth;
				this._productHeight = product._productHeight;
				this._productWieght = product._productWieght;
				this._productSizeUnit = product._productSizeUnit;
				this._productWeightUnit = product._productWeightUnit;
				this._itemInStock = product._itemInStock;
				this._sortToken = product._sortToken;

                break;
            }

            return (dataList.Count > 0 ? true : false);
        }

        /// <summary>
        /// Instance selection method. Used for loading multiple record based on provided conditions.
        /// </summary>
        /// <param name="conditions">Custom condition based on which to match data</param>
        /// <returns>List of instances of loaded objects</returns>
        public List<Product> Load(List<Condition> conditions)
        {
            List<Product> products = new List<Product>();

			SqlCommand command = (SqlCommand)CreateCommand();
			
            PrepareConditionalParameters(command, conditions);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_Product_LoadConditional.ToString());

            List<BaseDAO> dataList = GetListData(command);

            foreach (BaseDAO data in dataList)
            {
                Product product = (Product)data;
                products.Add(product);
            }

            return products;
        }

        /// <summary>
        /// Prepares the conditional parameters for use with Create/ Save/ Load/ Delete methods
        /// that use custom condition. Prepares the user Command object for using conditions.
        /// </summary>
        /// <param name="conditions">Custom condition based on which to match data</param>
        private void PrepareConditionalParameters(SqlCommand command, List<Condition> conditions)
        {
            StringBuilder condition = new StringBuilder();

            object id = null;
			object productName = null;
			object productDefaultImageUrl = null;
			object productPrice = null;
			object productBrandId = null;
			object productCategoryId = null;
			object mainProductId = null;
			object differBy = null;
			object isProductInSale = null;
			object saleTimePrice = null;
			object rating = null;
			object totalRater = null;
			object isActive = null;
			object isFeatured = null;
			object isNewArrival = null;
			object isBestSeller = null;
			object productLength = null;
			object productWidth = null;
			object productHeight = null;
			object productWieght = null;
			object productSizeUnit = null;
			object productWeightUnit = null;
			object itemInStock = null;
			object sortToken = null;

            for (int i = 0; i < conditions.Count; i++)
            {
                string fieldName = Utility.ToHungarianNotation(conditions[i].FieldName);

                if (conditions[i].JoiningCondition == JoinOperator.FirstValue)
                {
                    condition.Append("WHERE ");
                }
                else if (conditions[i].JoiningCondition == JoinOperator.And)
                {
                    condition.Append(" AND ");
                }
                else if (conditions[i].JoiningCondition == JoinOperator.Or)
                {
                    condition.Append(" OR ");
                }

                condition.Append(conditions[i].FieldName).Append(" ").Append(Condition.MapCondition(conditions[i].ConditionalOperator)).Append("@" + fieldName);

                if ((conditions[i].ConditionalOperator == ConditionOperator.Like ||
                    conditions[i].ConditionalOperator == ConditionOperator.NotLike))
                {
                    if (string.IsNullOrEmpty(conditions[i].Value.ToString()))
                        conditions[i].Value = "%%%";
                    else
                        conditions[i].Value = "%" + conditions[i].Value.ToString() + "%";
                }

                if (fieldName == TableColumns.Id.ToString())
                    id = conditions[i].Value;
                else if (fieldName == TableColumns.ProductName.ToString())
					productName = conditions[i].Value;
				else if (fieldName == TableColumns.ProductDefaultImageUrl.ToString())
					productDefaultImageUrl = conditions[i].Value;
				else if (fieldName == TableColumns.ProductPrice.ToString())
					productPrice = conditions[i].Value;
				else if (fieldName == TableColumns.ProductBrandId.ToString())
					productBrandId = conditions[i].Value;
				else if (fieldName == TableColumns.ProductCategoryId.ToString())
					productCategoryId = conditions[i].Value;
				else if (fieldName == TableColumns.MainProductId.ToString())
					mainProductId = conditions[i].Value;
				else if (fieldName == TableColumns.DifferBy.ToString())
					differBy = conditions[i].Value;
				else if (fieldName == TableColumns.IsProductInSale.ToString())
					isProductInSale = conditions[i].Value;
				else if (fieldName == TableColumns.SaleTimePrice.ToString())
					saleTimePrice = conditions[i].Value;
				else if (fieldName == TableColumns.Rating.ToString())
					rating = conditions[i].Value;
				else if (fieldName == TableColumns.TotalRater.ToString())
					totalRater = conditions[i].Value;
				else if (fieldName == TableColumns.IsActive.ToString())
					isActive = conditions[i].Value;
				else if (fieldName == TableColumns.IsFeatured.ToString())
					isFeatured = conditions[i].Value;
				else if (fieldName == TableColumns.IsNewArrival.ToString())
					isNewArrival = conditions[i].Value;
				else if (fieldName == TableColumns.IsBestSeller.ToString())
					isBestSeller = conditions[i].Value;
				else if (fieldName == TableColumns.ProductLength.ToString())
					productLength = conditions[i].Value;
				else if (fieldName == TableColumns.ProductWidth.ToString())
					productWidth = conditions[i].Value;
				else if (fieldName == TableColumns.ProductHeight.ToString())
					productHeight = conditions[i].Value;
				else if (fieldName == TableColumns.ProductWieght.ToString())
					productWieght = conditions[i].Value;
				else if (fieldName == TableColumns.ProductSizeUnit.ToString())
					productSizeUnit = conditions[i].Value;
				else if (fieldName == TableColumns.ProductWeightUnit.ToString())
					productWeightUnit = conditions[i].Value;
				else if (fieldName == TableColumns.ItemInStock.ToString())
					itemInStock = conditions[i].Value;
				else if (fieldName == TableColumns.SortToken.ToString())
					sortToken = conditions[i].Value;
            }

            AddCommandParameter(command, "@Condition", SqlDbType.NVarChar, 2000, ParameterDirection.Input, condition.ToString());
            AddCommandParameter(command, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, id);
			AddCommandParameter(command, "@ProductName", SqlDbType.NVarChar, 250, ParameterDirection.Input, productName);
			AddCommandParameter(command, "@ProductDefaultImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, productDefaultImageUrl);
			AddCommandParameter(command, "@ProductPrice", SqlDbType.Decimal, 0, ParameterDirection.Input, productPrice);
			AddCommandParameter(command, "@ProductBrandId", SqlDbType.Int, 0, ParameterDirection.Input, productBrandId);
			AddCommandParameter(command, "@ProductCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, productCategoryId);
			AddCommandParameter(command, "@MainProductId", SqlDbType.Int, 0, ParameterDirection.Input, mainProductId);
			AddCommandParameter(command, "@DifferBy", SqlDbType.NVarChar, 50, ParameterDirection.Input, differBy);
			AddCommandParameter(command, "@IsProductInSale", SqlDbType.Bit, 0, ParameterDirection.Input, isProductInSale);
			AddCommandParameter(command, "@SaleTimePrice", SqlDbType.Decimal, 0, ParameterDirection.Input, saleTimePrice);
			AddCommandParameter(command, "@Rating", SqlDbType.Decimal, 0, ParameterDirection.Input, rating);
			AddCommandParameter(command, "@TotalRater", SqlDbType.Int, 0, ParameterDirection.Input, totalRater);
			AddCommandParameter(command, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, isActive);
			AddCommandParameter(command, "@IsFeatured", SqlDbType.Bit, 0, ParameterDirection.Input, isFeatured);
			AddCommandParameter(command, "@IsNewArrival", SqlDbType.Bit, 0, ParameterDirection.Input, isNewArrival);
			AddCommandParameter(command, "@IsBestSeller", SqlDbType.Bit, 0, ParameterDirection.Input, isBestSeller);
			AddCommandParameter(command, "@ProductLength", SqlDbType.Decimal, 0, ParameterDirection.Input, productLength);
			AddCommandParameter(command, "@ProductWidth", SqlDbType.Decimal, 0, ParameterDirection.Input, productWidth);
			AddCommandParameter(command, "@ProductHeight", SqlDbType.Decimal, 0, ParameterDirection.Input, productHeight);
			AddCommandParameter(command, "@ProductWieght", SqlDbType.Decimal, 0, ParameterDirection.Input, productWieght);
			AddCommandParameter(command, "@ProductSizeUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, productSizeUnit);
			AddCommandParameter(command, "@ProductWeightUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, productWeightUnit);
			AddCommandParameter(command, "@ItemInStock", SqlDbType.Int, 0, ParameterDirection.Input, itemInStock);
			AddCommandParameter(command, "@SortToken", SqlDbType.NVarChar, 50, ParameterDirection.Input, sortToken);
        }

        /// <summary>
        /// Binds the fetched data from data reader to Objects
        /// </summary>
        /// <param name="reader">Data reader supplied by BaseDAO</param>
        /// <returns>List of object instances that was bound</returns>
        protected override List<BaseDAO> BindData(DbDataReader reader)
        {
            List<BaseDAO> result = new List<BaseDAO>();

			if (reader == null)
                return null;

            while (reader.Read())
            {
                Product product = new Product
                (
					reader["id"] == System.DBNull.Value ? GetIdMinValue : (int)reader["id"],
                    reader["product_name"] == System.DBNull.Value ? string.Empty : (string)reader["product_name"],
					reader["product_default_image_url"] == System.DBNull.Value ? string.Empty : (string)reader["product_default_image_url"],
					reader["product_price"] == System.DBNull.Value ? 0.00 : (double)reader["product_price"],
					reader["product_brand_id"] == System.DBNull.Value ? 0 : (int)reader["product_brand_id"],
					reader["product_category_id"] == System.DBNull.Value ? 0 : (int)reader["product_category_id"],
					reader["main_product_id"] == System.DBNull.Value ? 0 : (int)reader["main_product_id"],
					reader["differ_by"] == System.DBNull.Value ? string.Empty : (string)reader["differ_by"],
					reader["is_product_in_sale"] == System.DBNull.Value ? false : (bool)reader["is_product_in_sale"],
					reader["sale_time_price"] == System.DBNull.Value ? 0.00 : (double)reader["sale_time_price"],
					reader["rating"] == System.DBNull.Value ? 0.00 : (double)reader["rating"],
					reader["total_rater"] == System.DBNull.Value ? 0 : (int)reader["total_rater"],
					reader["is_active"] == System.DBNull.Value ? false : (bool)reader["is_active"],
					reader["is_featured"] == System.DBNull.Value ? false : (bool)reader["is_featured"],
					reader["is_new_arrival"] == System.DBNull.Value ? false : (bool)reader["is_new_arrival"],
					reader["is_best_seller"] == System.DBNull.Value ? false : (bool)reader["is_best_seller"],
					reader["product_length"] == System.DBNull.Value ? 0.00 : (double)reader["product_length"],
					reader["product_width"] == System.DBNull.Value ? 0.00 : (double)reader["product_width"],
					reader["product_height"] == System.DBNull.Value ? 0.00 : (double)reader["product_height"],
					reader["product_wieght"] == System.DBNull.Value ? 0.00 : (double)reader["product_wieght"],
					reader["product_size_unit"] == System.DBNull.Value ? string.Empty : (string)reader["product_size_unit"],
					reader["product_weight_unit"] == System.DBNull.Value ? string.Empty : (string)reader["product_weight_unit"],
					reader["item_in_stock"] == System.DBNull.Value ? 0 : (int)reader["item_in_stock"],
					reader["sort_token"] == System.DBNull.Value ? string.Empty : (string)reader["sort_token"]
                );

                result.Add(product);
            }
            
            if(!reader.IsClosed)
                reader.Close();

            return result;
        }

        /// <summary>
        /// List of store procedures that this class use
        /// </summary>
        private enum StoreProcedureName
        {
            Sp_Product_Save = 0,
            Sp_Product_Create = 1,
            Sp_Product_DeleteOne = 2,
            Sp_Product_DeleteMultiple = 3,
            Sp_Product_DeleteConditional = 4,
            Sp_Product_SaveConditional = 5,
            Sp_Product_LoadConditional = 6,
            Sp_Product_Load = 7,
            Sp_Product_LoadConditionalPaged = 8,
            Sp_Product_LoadConditionalPagedCount = 9,
            Sp_Product_LoadPaged = 10,
            Sp_Product_LoadPagedCount = 11
        }

        /// <summary>
        /// List of table column names that this class use
        /// </summary>
        private enum TableColumns
        {
            Id = 0,
			ProductName = 1,
			ProductDefaultImageUrl = 2,
			ProductPrice = 3,
			ProductBrandId = 4,
			ProductCategoryId = 5,
			MainProductId = 6,
			DifferBy = 7,
			IsProductInSale = 8,
			SaleTimePrice = 9,
			Rating = 10,
			TotalRater = 11,
			IsActive = 12,
			IsFeatured = 13,
			IsNewArrival = 14,
			IsBestSeller = 15,
			ProductLength = 16,
			ProductWidth = 17,
			ProductHeight = 18,
			ProductWieght = 19,
			ProductSizeUnit = 20,
			ProductWeightUnit = 21,
			ItemInStock = 22,
			SortToken = 23
        }
    }
}